﻿Imports System.ComponentModel

Public Class NewCasesForm

    Class NotesClass
        Public Note_Code As String
        Public TheDate As DateTime
        Public TheUser As String
        Public Note As String
    End Class

    Class CellClass
        Public FieldName As String
        Public RID As String
        Public FieldCode As Integer
        Public Value As Object
    End Class

    Class CaseFields
        Public FieldCode As Integer
        Public FieldName As String
        Public ModellingTable As String
    End Class

    Class FormulaClass
        Public FieldName As String
        Public Formula As String
    End Class

    Private Const EditColumnIndex As Integer = 8

    Dim FireEvents As Boolean = False
    Dim TheCase As String = ""
    Dim ThePeriod As Integer
    Dim TheName As String = ""
    Dim TheTerritory As String = ""
    Dim TheType As String = ""
    Dim ShowMenu As Boolean = False
    Dim ApprovedTotalFieldName As String = ""
    Dim MenuStatus As String
    Dim MenuIcon As Integer
    Dim SaveStuffCauseImNotLoading As Boolean = False
    Dim DidCellReallyChange As Boolean = False
    Dim CurrentNoteNumber As Integer
    Dim TheNotes As New List(Of NotesClass)
    Dim CurrentCell As New CellClass
    Dim TheFields As New List(Of CaseFields)
    Dim Formulas As New List(Of FormulaClass)


    Private Sub NewCases_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        FireEvents = False

        LoadPeriodBox()
        LoadCaseBox()
        LoadTypeBox()
        LoadTerritoryBox()
        LoadDataGrid()

        NotesBox.ReadOnly = True
        StatusBox.SelectedIndex = 0
        FireEvents = True

    End Sub

    Private Sub LoadPeriodBox()

        Dim SQL As String

        SQL = "Select * from Periods"

        If LoadComboBox(PeriodBox, SQL, "Period_ID", "Period_Name") = False Then
            MessageBox.Show("There has been an error loading the cases, please contact The Store administrator", "Period Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        PeriodBox.SelectedValue = CurrentPeriodID

        ThePeriod = PeriodBox.SelectedValue

    End Sub

    Private Sub LoadCaseBox()

        Dim SQL As String
        Dim TheData As DataView

        If CurrentUser.Level = 5 Then

            SQL = "Select DataCut_ID, Description from Modelling_Case_Datasets where Period_ID = '" & ThePeriod & "' Order By Description "

        Else

            SQL = "Select DataCut_ID, Description from Modelling_Case_Datasets where Period_ID = '" & ThePeriod & "' and Invisible = 0 Order By Description "

        End If


        TheData = LoadDataView(SQL)

        'If there are no cases then tell the user by updating the casesbox
        If TheData.Count = 0 Then
            SQL = "Select 'None' as DataCut_ID, 'No cases exist for the period " & PeriodBox.Text & "' as Description"
        End If

        If LoadComboBox(CaseBox, SQL, "DataCut_ID", "Description") = False Then
            MessageBox.Show("There has been an error loading the cases, please contact The Store administrator", "Cases Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        TheCase = CaseBox.SelectedValue.ToString

    End Sub

    Private Sub LoadTypeBox()

        Dim SQL As String

        SQL = "Select * from Budgets"

        If LoadComboBox(TypeBox, SQL, "Budget_ID", "Budget_Description") = False Then
            MessageBox.Show("There has been an error loading the cases, please contact The Store administrator", "Type Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        TheType = TypeBox.SelectedValue.ToString

    End Sub

    Private Sub LoadTerritoryBox()

        Dim SQL As String

        SQL = "Select '<All>' as Territory_Code, '<All>' as Territory_Name UNION ALL Select * from Territories"

        If LoadComboBox(TerritoryBox, SQL, "Territory_Code", "Territory_Name") = False Then
            MessageBox.Show("There has been an error loading the cases, please contact The Store administrator", "Territory Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        TheTerritory = TerritoryBox.SelectedValue.ToString

    End Sub

    Private Sub LoadDataGrid()
        ShowLoadingForm()

        Dim SQL As String
        Dim TheData As DataView
        SaveStuffCauseImNotLoading = False
        DidCellReallyChange = False

        Formulas.Clear()

        If CaseBox.Text.Contains("No cases exist for the period") Then
            DataGrid.DataSource = Nothing
            HideLoadingForm()

            Exit Sub
        End If

        SQL = BuildSQL()

        If SQL = "" Then Exit Sub

        DataGrid.DataSource = Nothing

        TheData = LoadDataView(SQL)
        DataGrid.DataSource = TheData


        'set some formatting
        SQL = "select * from Modelling_Case_Templates where DataCut_ID = '" & TheCase & "' and Period_ID = " & ThePeriod & " order by Field_Order"
        Dim TheView As DataView
        Dim dr As DataRowView

        TheView = LoadDataView(SQL)

        Dim FieldName As String
        Dim DataCut_ID As String

        For Each dr In TheView

            FieldName = dr.Item("Field_Name").ToString
            DataCut_ID = dr.Item("DataCut_ID").ToString

            Select Case dr.Item("Type").ToString
                Case "DATE"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Format = "d"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight
                Case "MONEY"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Format = "C"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight
                Case "PERCENT"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Format = "P"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight
                Case "INT"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Format = "#,0"
                    DataGrid.Columns(FieldName).DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight
                Case "YN"
                    'add a drop down box for Yes and No
                    Dim index As Integer

                    ' find the location of the column
                    index = DataGrid.Columns.IndexOf(DataGrid.Columns(FieldName))

                    ' remove the existing column
                    DataGrid.Columns.RemoveAt(index)

                    ' create a new custom column
                    Dim ComboCol As New DataGridViewComboBoxColumn
                    ComboCol.Items.Clear()
                    ComboCol.Items.Add("Y")
                    ComboCol.Items.Add("N")
                    ComboCol.Name = FieldName
                    ComboCol.DataPropertyName = FieldName

                    ' some more tweaking
                    ComboCol.SortMode = DataGridViewColumnSortMode.Automatic

                    ' insert the new column at the same location
                    DataGrid.Columns.Insert(index, ComboCol)

                Case Else
                    DataGrid.Columns(FieldName).DefaultCellStyle.Format = "G"
            End Select

            'Hide and show and size columns

            If dr.Item("Formula").ToString = "Formula" Then
                Dim itm As New FormulaClass
                itm.FieldName = FieldName
                itm.Formula = dr.Item("SQL").ToString

                Formulas.Add(itm)
            End If

            If dr.Item("Formula").ToString <> "User Input" Then
                'format the formula fields
                DataGrid.Columns(FieldName).Visible = True
                DataGrid.Columns(FieldName).ReadOnly = True
                DataGrid.Columns(FieldName).DefaultCellStyle.BackColor = Color.LightGray
                'DataGrid.Columns(FieldName).DefaultCellStyle.Font = New Font(Control.DefaultFont, FontStyle.Bold)
            Else
                'hide the notes fields
                DataGrid.Columns(FieldName & "_Notes").Visible = False
            End If


        Next

        DataGrid.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter

        DataGrid.Columns("A").Visible = False
        DataGrid.Columns("A").Frozen = True
        DataGrid.Columns("A").ReadOnly = True

        DataGrid.Columns("AImage").Visible = True
        DataGrid.Columns("AImage").Frozen = True
        DataGrid.Columns("AImage").ReadOnly = True
        DataGrid.Columns("AImage").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCellsExceptHeader
        DataGrid.Columns("AImage").MinimumWidth = DataGridViewAutoSizeColumnMode.ColumnHeader

        DataGrid.Columns("RID").Visible = False
        DataGrid.Columns("RID").Frozen = True
        DataGrid.Columns("RID").ReadOnly = True

        DataGrid.Columns("UPIN").Frozen = True
        DataGrid.Columns("UPIN").Visible = True
        DataGrid.Columns("UPIN").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        DataGrid.Columns("UPIN").ReadOnly = True
        DataGrid.Columns("UPIN").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

        DataGrid.Columns("Provider").Frozen = True
        DataGrid.Columns("Provider").Visible = True
        DataGrid.Columns("Provider").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        DataGrid.Columns("Provider").ReadOnly = True
        DataGrid.Columns("UPIN").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        'DataGrid.Columns("Provider").Resizable = DataGridViewTriState.True

        DataGrid.Columns("Territory").Frozen = True
        DataGrid.Columns("Territory").Visible = True
        DataGrid.Columns("Territory").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        DataGrid.Columns("Territory").ReadOnly = True
        DataGrid.Columns("Territory").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

        DataGrid.Columns("Region").Frozen = True
        DataGrid.Columns("Region").Visible = False
        'DataGrid.Columns("Region").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        'DataGrid.Columns("Region").ReadOnly = True
        'DataGrid.Columns("Region").AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells

        DataGrid.Columns("Local Authority").Frozen = True
        DataGrid.Columns("Local Authority").Visible = True
        DataGrid.Columns("Local Authority").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        DataGrid.Columns("Local Authority").ReadOnly = True
        DataGrid.Columns("Local Authority").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

       


        For c = 0 To DataGrid.Columns.Count - 1
            DataGrid.Columns(c).Resizable = DataGridViewTriState.True
            If c >= 8 Then
                DataGrid.Columns(c).SortMode = DataGridViewColumnSortMode.Programmatic
            End If
        Next c



        Me.Cursor = Cursors.Default

        'If CurrentUser.Level < 1 Then
        '    DataGrid.ReadOnly = True
        '    AttachmentStrip.Enabled = False
        '    NotesBox.ReadOnly = True
        'End If

        If UserFormCheck(Me.Name) <> "W" Then
            DataGrid.ReadOnly = True
            AttachmentStrip.Enabled = False
            NotesBox.ReadOnly = True
        End If

        DataGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText


        'Set Formats of Cells which have been approved/rejected
        GetStatus(False, "")
        UpdateApprovedValues()
        DataGrid.Refresh()

        HideLoadingForm()
        SaveStuffCauseImNotLoading = True
        DidCellReallyChange = True

    End Sub

    Private Sub PeriodBox_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged

        If FireEvents = False Then Exit Sub

        ClearFilters()

        ThePeriod = PeriodBox.SelectedValue

        LoadCaseBox()

    End Sub

    Private Sub CaseBox_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CaseBox.SelectedIndexChanged

        If FireEvents = False Then Exit Sub

        ClearFilters()

        TheCase = CaseBox.SelectedValue.ToString

        LoadDataGrid()

    End Sub

    Private Sub TerritoryBox_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoryBox.SelectedIndexChanged

        If FireEvents = False Then Exit Sub

        ClearFilters()

        TheTerritory = TerritoryBox.SelectedValue.ToString

        LoadDataGrid()

    End Sub

    Private Sub TypeBox_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TypeBox.SelectedValueChanged

        If FireEvents = False Then Exit Sub

        ClearFilters()

        TheType = TypeBox.SelectedValue.ToString

        LoadDataGrid()

    End Sub

    Private Sub ClearButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearButton.Click

        If FireEvents = False Then Exit Sub

        ClearFilters()
        TerritoryBox.SelectedValue = "<All>"
        LoadDataGrid()


    End Sub

    Sub ClearFilters()

        SearchBox.Text = ""
        TheName = ""

    End Sub

    Private Sub SearchBox_MouseHover(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchBox.MouseHover

        ToolTip1.SetToolTip(SearchBox, "Type in this box to search for a provider by name, then hit the enter key")
        ToolTip1.Active = True

    End Sub

    Function BuildSQL() As String

        Dim sql As String
        Dim subsql As String
        Dim TheData As DataView

        ApprovedTotalFieldName = ""

        sql = "Select * from Modelling_Case_Templates where DataCut_ID ='" & TheCase & "' and Period_ID = " & ThePeriod & " Order By Field_Order "

        TheData = LoadDataView(sql)

        sql = "Select A, Cast(Null as Image) as AImage, RID, UPIN, Provider, Territory, Region, [Local Authority] "

        Dim Record As DataRowView

        Dim SelectClause As String = ""

        'This gets the latest value from the data table for the case/period specified
        Dim DataJoin As String

        DataJoin = "Select d.RID, d.Field_Code, d.Value, d.Datacut_ID from modelling_case_data d, " & _
                    " " & _
                    "(select Datacut_ID, rid, Field_Code, max(TheDate) as MaxDate from modelling_case_data " & _
                    "Group by  " & _
                    "Datacut_ID, rid, Field_Code) maxd " & _
                    " " & _
                    "where maxd.datacut_ID = d.Datacut_ID " & _
                    "and maxd.RID = d.RID " & _
                    "and maxd.FIeld_Code = d.Field_Code " & _
                    "and maxd.MaxDate = D.TheDate and d.Datacut_ID = '" & TheCase & "' and d.Period_ID = " & ThePeriod

        Dim FromClause As String = " From All_Provider_Fields p Left Outer JOIN ( " & DataJoin & " ) d on (p.Rid=d.Rid) " & _
                                    "Left Outer Join (select * from Provider_Funding_Streams where Period_ID = " & PeriodBox.SelectedValue & ") f on (p.UPIN=f.UPIN) " & _
                                    "Left Outer Join Budgets b on (f.Budget_ID=b.Budget_ID) " & _
                                    "Left Outer Join (Select RID, Field_Code, COUNT(Field_Code) as 'Notes' from modelling_case_notes where datacut_id = '" & TheCase & "' and Period_ID = " & ThePeriod & " Group By RID, Field_Code) N on (N.Rid = p.RID and N.Field_Code=d.Field_Code) " & _
                                    "Left Outer Join (Select RID, COUNT(RID) As A from dbo.Case_Attachments where Datacut_ID = '" & TheCase & "' Group By RID) A on (P.RID=A.RID) "

        'Clear TheFields
        TheFields.Clear()
        TheNotes.Clear()
        Formulas.Clear()

        For Each Record In TheData

            'Whilst building SQL code add all fields into a list
            Dim FieldItem As New CaseFields

            FieldItem.FieldCode = Record.Item("Field_Code")
            FieldItem.FieldName = Record.Item("Field_Name")
            FieldItem.ModellingTable = Record.Item("Modelling_Table")
            TheFields.Add(FieldItem)


            If Record.Item("Formula").ToString = "User Input" Then
                SelectClause = SelectClause & ", (case when d.Field_Code = " & Record.Item("Field_Code") & " then coalesce(Value,0) else 0 end) as [" & _
                           Record.Item("Field_Code") & "], (case when d.Field_Code = " & Record.Item("Field_Code") & " then coalesce(Notes,0) else 0 end) as [" & _
                           Record.Item("Field_Code") & "_Notes] "

                sql = sql & ", SUM([" & Record.Item("Field_Code") & "]) as [" & Record.Item("Field_Name") & "], SUM([" & Record.Item("Field_Code") & "_Notes]) as [" & _
                      Record.Item("Field_Name") & "_Notes] "

            ElseIf Record.Item("Formula").ToString = "Formula" Then

                sql = sql & ", SUM(" & FixFormula(Record.Item("SQL").ToString) & ") as [" & Record.Item("Field_Name") & "] "

            ElseIf Record.Item("Formula").ToString = "Approved Total" Then

                'find what is actually the current approved figure


                sql = sql & ", cast(0 as decimal(18,6)) as [" & Record.Item("Field_Name") & "] "
                ApprovedTotalFieldName = Record.Item("Field_Name").ToString

            ElseIf Record.Item("Formula").ToString = "Product" Then

                'Its a Product
                Dim TheInfo As String
                TheInfo = Trim(Record.Item("SQL").ToString())
                Dim TheSlash As Integer
                TheSlash = InStr(TheInfo, "/")
                Dim TheScenario As String
                Dim TheProduct As String
                TheScenario = Trim(Mid(TheInfo, 1, TheSlash - 1))
                TheProduct = Trim(Mid(TheInfo, TheSlash + 1, Len(TheInfo)))

                Dim productsql As String

                productsql = Modelling_ResultsForm.StageSQL(TheScenario, "", TheProduct, "Product", False)

                SelectClause = SelectClause & ", (case when d.Field_Code = " & Record.Item("Field_Code") & " then coalesce(Value,0) else 0 end) as [" & _
                           Record.Item("Field_Code") & "] "

                sql = sql & ", SUM([" & Record.Item("Field_Code") & "]) as [" & Record.Item("Field_Name") & "] "

                'Add the product info into the Values table with a Union All

                DataJoin = DataJoin & " union all Select RID, " & Record.Item("Field_Code") & " as [Field_Code], [Final Result] as [Value], '" & TheInfo & _
                    "' as [DataCut_ID] from (" & productsql & ") sub"

                FromClause = " From All_Provider_Fields p Left Outer JOIN ( " & DataJoin & " ) d on (p.Rid=d.Rid) " & _
                                  "Left Outer Join (select * from Provider_Funding_Streams where period_ID = " & PeriodBox.SelectedValue & ") f on (p.UPIN=f.UPIN) " & _
                                  "Left Outer Join Budgets b on (f.Budget_ID=b.Budget_ID) " & _
                                  "Left Outer Join (Select RID, Field_Code, COUNT(Field_Code) as 'Notes' from modelling_case_notes where datacut_id = '" & TheCase & "' and Period_ID = " & ThePeriod & " Group By RID, Field_Code) N on (N.Rid = p.RID and N.Field_Code=d.Field_Code) " & _
                                  "Left Outer Join (Select RID, COUNT(RID) As A from dbo.Case_Attachments where Datacut_ID = '" & TheCase & "' Group By RID) A on (P.RID=A.RID) "

            End If

        Next

        'Set Filters by building where clause
        Dim subsqlwhere As String

        subsqlwhere = " where p.lead = 1  and b.Budget_ID = '" & TheType & "' "

        If TheTerritory <> "<All>" Then
            subsqlwhere = subsqlwhere & " and p.Territory_Code = '" & TheTerritory & "' "
        End If

        If TheName <> "" Then
            subsqlwhere = subsqlwhere & " and p.Provider_Name like '%" & TheName & "%' "
        End If

        subsql = "Select Coalesce(a.A, 0) as [A], p.RID as 'RID', p.UPIN as 'UPIN', p.Provider_Name as 'Provider', b.Budget_ID, b.Budget_Description as 'Type', " & _
             "p.LA_Name as 'Local Authority', p.Region_Name as 'Region', p.Territory_Name as 'Territory', d.Field_Code as 'FieldCode' " & SelectClause & FromClause & _
             subsqlwhere


        sql = sql & " from (" & subsql & ") subsql Group by A, RID, UPIN, Provider, Type,[Local Authority] , Region, Territory ORDER BY Provider "

        If TheData.Count = 0 Then sql = ""

        BuildSQL = sql

    End Function




    Private Sub DataGrid_CellToolTipTextNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellToolTipTextNeededEventArgs) Handles DataGrid.CellToolTipTextNeeded

    End Sub

    Private Sub DataGrid_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGrid.CellValueChanged

        If DidCellReallyChange = False Then Exit Sub

        If SaveStuffCauseImNotLoading = False Then Exit Sub

        If FireEvents = False Then Exit Sub
        If DataGrid.Columns(e.ColumnIndex).Name.Contains("_Notes") Then Exit Sub

        If e.ColumnIndex >= EditColumnIndex Then

            'Check to see if cell is in a formula column, if so exit as we do not want to write back value to database
            For i = 0 To Formulas.Count - 1
                If DataGrid.Columns(e.ColumnIndex).Name = Formulas(i).FieldName.ToString Then
                    Exit Sub
                End If
            Next

            'If not a formula then save the Cell
            Dim r, c As Integer

            Dim sql As String = ""

            Dim TheRid As String
            Dim TheValue As Decimal
            Dim TheFieldCode As Integer
            Dim TheFieldName As String

            Dim TheDate As Double = Now.ToOADate

            With DataGrid

                If e.ColumnIndex < EditColumnIndex Then Exit Sub

                c = e.ColumnIndex
                r = e.RowIndex

                TheFieldName = DataGrid.Columns(c).Name.ToString
                TheFieldCode = FieldCode(TheFieldName)
                TheRid = DataGrid.Item("RID", r).Value.ToString
                TheValue = DataGrid.Item(c, r).Value

                sql = "Insert into Modelling_Case_Data Values ('" & TheCase & "', " & ThePeriod & ", '" & TheRid & "', " & TheFieldCode & ", " & _
                    TheDate & ", '" & GetUserName & "', 'Unsupported', " & TheValue & ") "

                RunNonQuery(sql)

                DataGrid.Item(e.ColumnIndex, e.RowIndex).Tag = "Changed"

                'GetStatus(False, "")
                'UpdateApprovedValues()
                DataGrid.Refresh()

            End With

        End If

    End Sub

    Private Sub NewNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewNoteBut.Click

        If DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Index < EditColumnIndex Then
            Exit Sub 'as this is a header column
        End If

        NotesBox.ReadOnly = False

        NotesCountLabel.Text = "New"

        NotesBox.Text = ""
        NotesBox.ReadOnly = False

        NotesLabel.Text = FormatDateTime(Now, DateFormat.LongDate) & " " & FormatDateTime(Now, DateFormat.ShortTime) & " - " & CurrentUser.Name

        NotesBox.Focus()
    End Sub

    Private Sub DeleteNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteNoteBut.Click
        'can I delete you?

        If CurrentNoteNumber = -1 Then Exit Sub

        If TheNotes(CurrentNoteNumber).TheUser = CurrentUser.Name Then

            'only allow deletion if this is the last note (which is the first one)
            If CurrentNoteNumber = 0 Then

                If MessageBox.Show("Are you sure you want to delete this note?", "Delete note?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then Exit Sub

                'ok delete the note
                Dim sql As String


                sql = "delete from Modelling_Case_Notes where Note_Code = '" & TheNotes(CurrentNoteNumber).Note_Code & "'"
                RunNonQuery(sql)

                'reload the cell
                'first update the notes field
                Dim notesfield As String
                Dim value As Integer

                notesfield = DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name & "_Notes"
                Try
                    value = DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value
                    value = value - 1
                    DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value = value

                Catch ex As Exception
                    'there are no notes for this field

                    Exit Sub
                End Try

                UpdateNotesView()

            Else
                MessageBox.Show("You cannot delete this note, because it has been archived", "Cannot delete note", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

        Else
            MessageBox.Show("You cannot delete this note, because it does not belong to you", "Cannot delete note", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub UpdateNotesView()
        Dim value As Integer

        Dim notesfield As String

        'clear the notes
        NotesBox.Text = ""
        NotesLabel.Text = "Notes"
        NotesCountLabel.Text = "0 of 0"
        CurrentNoteNumber = -1

        TheNotes.Clear()

        notesfield = DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name & "_Notes"
        Try
            value = DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value
        Catch ex As Exception

            'If there is no notes column then show the case guidance notes
            With DataGrid

                Dim TheNotes As String
                Dim notessql As String

                notessql = "Select Notes from Modelling_Case_Datasets where DataCut_Id = '" & TheCase & "' and Period_ID = " & ThePeriod

                Dim TheData As DataView

                TheData = LoadDataView(notessql)

                TheNotes = TheData(0).Item("Notes")

                NotesBox.Text = TheNotes

                NotesLabel.Text = "Case Guidance Notes"
                NotesCountLabel.Visible = False
                NewNoteBut.Visible = False
                DeleteNoteBut.Visible = False
                PreviousNoteBut.Visible = False
                NextNoteBut.Visible = False
                NotesBox.ReadOnly = True

            End With

            Exit Sub

        End Try

        NotesCountLabel.Visible = True
        NewNoteBut.Visible = True
        DeleteNoteBut.Visible = True
        DeleteNoteBut.Enabled = False
        PreviousNoteBut.Visible = True
        NextNoteBut.Visible = True
        NotesBox.ReadOnly = False

        If value = 0 Then Exit Sub

        'load the first note
        Dim sql As String

        sql = "select * from modelling_case_notes where Datacut_ID = '" & TheCase & _
              "' and Period_ID = " & ThePeriod & " and RID = '" & CurrentCell.RID & _
              "' and Field_Code = " & CurrentCell.FieldCode & " order by TheDate desc"

        Dim theview As DataView

        theview = LoadDataView(sql)

        'load the list
        Dim r As DataRowView

        For Each r In theview
            Dim itm As New NotesClass

            itm.Note_Code = r.Item("Note_Code").ToString
            itm.Note = r.Item("Note").ToString
            itm.TheDate = r.Item("TheDate")
            itm.TheUser = Trim(r.Item("User_Name").ToString)

            TheNotes.Add(itm)

        Next

        LoadNote(0)

        If UserFormCheck(Me.Name) <> "W" Then
            NotesBox.ReadOnly = True
        End If

    End Sub

    Private Sub LoadNote(ByVal NoteNumber As Integer)

        If TheNotes.Count = 0 Then Exit Sub

        NotesCountLabel.Visible = True
        NewNoteBut.Visible = True
        DeleteNoteBut.Visible = True
        PreviousNoteBut.Visible = True
        NextNoteBut.Visible = True
        NotesBox.ReadOnly = False

        NotesCountLabel.Text = NoteNumber + 1 & " of " & TheNotes.Count

        NotesBox.Text = TheNotes(NoteNumber).Note
        NotesLabel.Text = FormatDateTime(TheNotes(NoteNumber).TheDate, DateFormat.LongDate) & " " & FormatDateTime(TheNotes(NoteNumber).TheDate, DateFormat.ShortTime) & " - " & TheNotes(NoteNumber).TheUser

        If TheNotes(NoteNumber).TheUser = CurrentUser.Name Then
            DeleteNoteBut.Enabled = True

            If NoteNumber = 0 Then
                NotesBox.ReadOnly = False
            Else
                NotesBox.ReadOnly = True
            End If
        Else
            DeleteNoteBut.Enabled = False
            NotesBox.ReadOnly = True
        End If

        CurrentNoteNumber = NoteNumber

    End Sub

    Private Sub SaveNote()
        If UserFormCheck(Me.Name) <> "W" Then
            Exit Sub
        End If

        If NotesCountLabel.Text = "New" Or NotesCountLabel.Text = "Edit" Then

            Dim sql As String

            'lets delete the note first and then we add it back again (but only if we are editing)
            If NotesCountLabel.Text = "Edit" Then
                If CurrentNoteNumber <> -1 Then
                    sql = "delete from Modelling_Case_Notes where Note_Code = '" & TheNotes(CurrentNoteNumber).Note_Code & "'"
                    RunNonQuery(sql)
                End If
            End If


            If Trim(NotesBox.Text) = "" Then Exit Sub

            Dim ADateNum As Double
            Dim ADate As DateTime
            Dim NoteCode As String

            ADate = Now
            ADateNum = ADate.ToOADate - 2

            NoteCode = Mid(CurrentUser.Name, 1, 3) & Format(ADate, "ddMMyyyyHHmmss")

            sql = "insert into Modelling_Case_Notes (DataCut_ID, Period_ID, RID, Field_Code, TheDate, User_Name, Note, Note_Code) Values ('" & _
                TheCase & "', " & ThePeriod & ", '" & CurrentCell.RID & "', " & CurrentCell.FieldCode & ", " & ADateNum & ", '" & CurrentUser.Name & "', '" & RemoveBadChars(NotesBox.Text) & "', '" & NoteCode & "')"
            RunNonQuery(sql)

            Dim value As Integer
            Dim notesfield As String

            notesfield = DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name & "_Notes"
            Try
                value = DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value
                value = value + 1
                DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value = value

            Catch ex As Exception
                'there are no notes for this field

                Exit Sub
            End Try

            'save a blank field to the values table if there is no field yet
            If IsDBNull(DataGrid.CurrentCell.Value) Then
                'first set the older values to history
                sql = "update Modelling_Case_Notes set Status = 'History' where DataCut_ID = '" & TheCase & "' and Period_ID = " & ThePeriod & " and RID = '" & CurrentCell.RID & "' and Field_Code = " & CurrentCell.FieldCode & " and Status = 'Approved'"
                RunNonQuery(sql)

                'delete any other stupid nulls
                sql = "delete from Modelling_Case_Notes where value is null and DataCut_ID = '" & TheCase & "' and Period_ID = " & ThePeriod & " and RID = '" & CurrentCell.RID & "' and Field_Code = " & CurrentCell.FieldCode
                RunNonQuery(sql)

                sql = "insert into Modelling_Case_Notes (DataCut_ID, Period_ID, RID, Field_Code, TheDate, User_Name, Status, Value) values('" & _
                             TheCase & "', " & ThePeriod & ", '" & CurrentCell.RID & "', " & CurrentCell.FieldCode & ", " & Now.ToOADate - 2 & ", '" & CurrentUser.Name & "', 'Approved', null)"
                RunNonQuery(sql)
            End If

            UpdateNotesView()

        End If
    End Sub

    Private Sub NotesBox_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NotesBox.Leave
        SaveNote()
    End Sub

    Private Sub DataGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGrid.CellEnter

        'Load Info into CurrentCell Class

        CurrentCell.FieldName = DataGrid.Columns(e.ColumnIndex).Name.ToString

        If DataGrid.Columns(e.ColumnIndex).ReadOnly = True Then
            CurrentCell.FieldCode = 0
            ' NotesBox.Enabled = False
        Else
            CurrentCell.FieldCode = FieldCode(CurrentCell.FieldName)
            ' NotesBox.Enabled = True
        End If

        CurrentCell.RID = DataGrid.Item("RID", e.RowIndex).Value.ToString
        CurrentCell.Value = DataGrid.Item(e.ColumnIndex, e.RowIndex).Value


        UpdateHistoryView()
        UpdateNotesView()
        LoadAttachmentsBox()

    End Sub

    Private Sub UpdateHistoryView()
        HistoryList.Items.Clear()
        HistoryLabel.Text = "History"
        If DataGrid.SelectedCells.Count = 0 Then Exit Sub

        If DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).ReadOnly = True Then Exit Sub

        If CurrentCell.FieldCode = 0 Then Exit Sub

        HistoryLabel.Text = DataGrid.Item(DataGrid.Columns("Provider").Index, DataGrid.CurrentRow.Index).Value & " - " & CurrentCell.FieldName

        Dim sql As String
        Dim TheView As DataView

        sql = "select * from Modelling_Case_Data where DataCut_Id = '" & TheCase & "' and Period_ID = " & ThePeriod & " and RID = '" & CurrentCell.RID & "' and Field_Code = " & CurrentCell.FieldCode & " order by TheDate desc"

        TheView = LoadDataView(sql)

        Dim itm As ListViewItem
        Dim str(3) As String
        Dim rec As DataRowView

        For Each rec In TheView
            str(0) = FormatDateTime(rec.Item("TheDate"), DateFormat.ShortDate) & " " & FormatDateTime(rec.Item("TheDate"), DateFormat.ShortTime)
            str(1) = Trim(rec.Item("User_Name").ToString)

            If IsDBNull(rec.Item("Value")) Then
                str(2) = "Blank"
            Else
                str(2) = Format(rec.Item("Value"), "#,0.######")
            End If

            itm = New ListViewItem(str)

            HistoryList.Items.Add(itm)

        Next

    End Sub

    Private Sub PreviousNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PreviousNoteBut.Click
        If CurrentNoteNumber = -1 Then Exit Sub
        If CurrentNoteNumber = 0 Then Exit Sub

        LoadNote(CurrentNoteNumber - 1)

    End Sub

    Private Sub NextNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextNoteBut.Click
        If CurrentNoteNumber = -1 Then Exit Sub
        If CurrentNoteNumber = TheNotes.Count - 1 Then Exit Sub

        LoadNote(CurrentNoteNumber + 1)
    End Sub

    Function FieldCode(ByVal FieldName As String) As Integer

        If TheFields.Count = 0 Then
            Return 0
            Exit Function
        End If

        For r = 0 To TheFields.Count - 1
            If TheFields(r).FieldName = FieldName Then
                Return TheFields.Item(r).FieldCode
                Exit Function
            End If
        Next

        Return 0

    End Function

    Function FieldName(ByVal FieldCode As Integer) As String

        If TheFields.Count = 0 Then
            Return ""
            Exit Function
        End If

        For r = 0 To TheFields.Count - 1
            If TheFields(r).FieldCode = FieldCode Then
                Return TheFields(r).FieldName
                Exit Function
            End If
        Next

        Return ""

    End Function


    Private Function FixFormula(ByVal Formula As String) As String


        'this does not do anything...

        'step through the formula and make sure that all the periods will exist other wise replace it with a 0

        Dim S As String = Formula
        Dim Field As String = ""

        While S.Contains("]")

            'find the field
            Field = Mid(S, S.IndexOf("[") + 1, S.IndexOf("]") - S.IndexOf("[") + 1)

            S = S.Replace(Field, "")

        End While



        Return Formula
    End Function

    Private Sub DataGrid_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles DataGrid.DataError
        MessageBox.Show(Me, "You can only enter numbers in the cases section - you cannot enter text", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

    Private Sub RefreshFormulas(ByVal row As Integer)

        If FireEvents = False Then Exit Sub

        If Formulas.Count = 0 Then Exit Sub


        Dim itm As New FormulaClass
        Dim Value As Double

        For Each itm In Formulas

            Dim TheFormula As String

            TheFormula = itm.Formula

            'now that you have the formula replace it with the field codes
            'find the [] and replace them

            While TheFormula.Contains("[")
                'find the start and end of the first field and replace it
                Dim s, e As Integer
                Dim TheCode As String
                Dim TheName As String
                Dim column As DataGridViewColumn


                s = TheFormula.IndexOf("[")
                e = TheFormula.IndexOf("]")

                If s = -1 Or e = -1 Then
                    'something is wrong so exit
                    Exit Sub
                Else
                    TheCode = TheFormula.Substring(s + 1, (e - s) - 1)

                    'find the column and replace it with its value
                    'PeriodNum = TheCode.Substring(4, (Len(TheCode) - TheCode.IndexOf("P")) - 1)

                    TheName = FieldName(TheCode)

                    If TheName <> "" Then
                        For Each column In DataGrid.Columns
                            If column.Name = TheName Then
                                Try
                                    Value = DataGrid.Item(DataGrid.Columns(column.Name).Index, row).Value
                                Catch ex As Exception
                                    Value = 0
                                End Try

                                TheFormula = TheFormula.Replace("[" & TheCode & "]", Value.ToString)

                            End If
                        Next
                    Else
                        'it could not find the field so remove it from the formula
                        TheFormula = TheFormula.Replace("[" & TheCode & "]", "0")
                    End If


                End If

            End While


            If TheFormula.Contains("/0") Then
                Value = Nothing
            Else
                'Value = New DataTable().Compute(TheFormula, "")

                Dim thea As DataTable

                thea = LoadDataTable("select " & TheFormula & " as Value")
                Value = thea(0).Item("Value")

            End If

            DataGrid.Item(DataGrid.Columns(itm.FieldName).Index, row).Value = Value

            'Reset the Cell tags, and check for latest status.
            DataGrid.Item(DataGrid.Columns(itm.FieldName).Index, row).Tag = "Unsupported"
            DataGrid.Item(DataGrid.Columns(itm.FieldName).Index, row).Style.BackColor = Color.LightGray
            GetStatus(True, DataGrid.Item("RID", row).Value.ToString)
            UpdateApprovedValues()

        Next
    End Sub


    Private Sub DataGrid_CellValidated(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGrid.CellValidated

        If FireEvents = False Then Exit Sub

        RefreshFormulas(e.RowIndex)

    End Sub

    Private Sub NotesBox_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles NotesBox.KeyUp
        If NotesBox.ReadOnly = True Then Exit Sub

        If NotesCountLabel.Text = "New" Then Exit Sub

        NotesCountLabel.Text = "Edit"
    End Sub

    Private Sub AddAttachmentButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddAttachmentButton.Click

        FileDialog.FileName = ""
        FileDialog.Title = "Add attachment"
        If FileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            With FileDialog



                Dim DataCut_ID As String
                Dim RID As String


                DataCut_ID = CaseBox.SelectedValue.ToString


                RID = CurrentCell.RID


                Try
                    ShowLoadingForm()

                    'SaveAttachment(DataCut_ID, RID, RemoveBadChars(.SafeFileName), .FileName)
                    CaseNotesForm.InsertFile(.FileName, "uspInsertFile", DataCut_ID, RID, RemoveBadChars(.SafeFileName))

                Catch ex As Exception
                    HideLoadingForm()
                    MessageBox.Show("There has been an error, please take a screen print and send it to TheStore.EFA@education.gsi.gov.uk:  " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try

                LoadItemInBox(RemoveBadChars(.SafeFileName))
                HideLoadingForm()

                DataGrid.Item("A", DataGrid.CurrentRow.Index).Value = AttachmentsBox.Items.Count
                DataGrid.Refresh()




            End With
        End If
    End Sub

    Private Sub LoadItemInBox(ByVal TheFileName As String)

        Dim ImageIndex As Integer

        If TheFileName.ToString.Contains(".doc") Then
            ImageIndex = 1
        Else
            If TheFileName.ToString.Contains(".xls") Then
                ImageIndex = 2
            Else
                If TheFileName.ToString.Contains(".pdf") Then
                    ImageIndex = 3
                Else
                    ImageIndex = 0
                End If
            End If
        End If


        AttachmentsBox.Items.Add(TheFileName, ImageIndex)
    End Sub

    Private Sub RemoveAttachmentButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemoveAttachmentButton.Click
        DeleteAttachements()
    End Sub

    Private Sub DeleteAttachements()
        If AttachmentsBox.SelectedItems.Count = 0 Then Exit Sub
        Dim sql As String
        Dim Rid As String
        Dim DataCut_ID As String


        Rid = CurrentCell.RID

        DataCut_ID = CaseBox.SelectedValue.ToString


        Dim FileName As String
        With AttachmentsBox
            FileName = .SelectedItems(0).Text
        End With

        'can I delete this?

        sql = "select * from Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and FileName = '" & FileName & "'"

        Dim AView As DataView
        AView = LoadDataView(sql)

        If AView(0).Item("User_Name") <> CurrentUser.Name Then
            MessageBox.Show("You cannot delete this attachment.  It belongs to " & AView(0).Item("User_Name"), "Delete not allowed", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If MessageBox.Show("Are you sure you want to delete this attachment?", "Delete attachment?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If



        sql = "delete from Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and FileName = '" & FileName & "'"

        RunNonQuery(sql)
        LoadAttachmentsBox()

        DataGrid.Item("A", DataGrid.CurrentRow.Index).Value = AttachmentsBox.Items.Count
        DataGrid.Refresh()


    End Sub

    Private Sub LoadAttachmentsBox()
        AttachmentsBox.Items.Clear()

        Dim sql As String
        Dim DataCut_ID As String
        Dim Rid As String
        Dim r As DataRowView
        Dim AView As DataView


        Rid = CurrentCell.RID

        DataCut_ID = CaseBox.SelectedValue.ToString


        sql = "SELECT DataCut_ID, RID, FileName, User_Name FROM Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "'"
        AView = LoadDataView(sql)

        For Each r In AView
            LoadItemInBox(r.Item("FileName"))
        Next
    End Sub

    Private Sub AttachmentsBox_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AttachmentsBox.DoubleClick
        LoadTheAttachment()
    End Sub

    Private Sub LoadTheAttachment()

        If AttachmentsBox.SelectedItems.Count = 0 Then Exit Sub
        ShowLoadingForm()
        Me.Cursor = Cursors.WaitCursor
        Dim FileName As String
        With AttachmentsBox
            FileName = .SelectedItems(0).Text
        End With

        Dim TempFile As String
        TempFile = Environment.GetEnvironmentVariable("Temp") & "\" & FileName

        Try
            Dim strConn As String = StoreConnect
            Dim NewString As String

            NewString = strConn

            ' NewString = NewString.Substring(0, Len(NewString) - 4)
            '   NewString = NewString & "SSPI"
            Dim sql As String
            Dim Rid As String
            Dim DataCut_ID As String

            Rid = CurrentCell.RID


            DataCut_ID = CaseBox.SelectedValue.ToString


            sql = "Select * from Case_Attachments where DataCut_ID = '" & DataCut_ID & "' and RID = '" & Rid & "' and FileName = '" & FileName & "'"

            Dim da As New OleDb.OleDbDataAdapter(sql, "Provider=SQLOLEDB;" & NewString)
            'Dim da As New OleDb.OleDbDataAdapter(sql, strConn)
            Dim ds As New DataSet()
            Dim cb As New OleDb.OleDbCommandBuilder(da)

            da.Fill(ds)

            Dim arr() As Byte
            arr = ds.Tables(0).Rows(0).Item("Attachment")

            FileOpen(1, TempFile, OpenMode.Binary)
            FilePut(1, arr)
            FileClose(1)
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            HideLoadingForm()
            MessageBox.Show("There has been an error, please take a screen print and send it to TheStore.EFA@education.gsi.gov.uk:  " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try


        Dim myProcess As New Process()

        Try
            myProcess.StartInfo.UseShellExecute = True
            myProcess.StartInfo.FileName = TempFile
            myProcess.Start()
            '            System.IO.Directory.SetCurrentDirectory(StoreLocalPath)
            '           System.Diagnostics.Process.Start(StoreLocalPath & "The Store.exe")
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            HideLoadingForm()
            MessageBox.Show("There has been an error, please take a screen print and send it to TheStore.EFA@education.gsi.gov.uk:  " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Cursor = Cursors.Default
        End Try
        Me.Cursor = Cursors.Default
        HideLoadingForm()

    End Sub

    Private Sub DataGrid_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGrid.CellPainting

        'If FireEvents = False Then Exit Sub
        If CaseBox.SelectedValue Is Nothing Then Exit Sub

        'Attachment Header Cell
        Dim pt As Point

        If e.RowIndex = -1 And DataGrid.Columns(e.ColumnIndex).Name = "AImage" Then

            pt = e.CellBounds.Location

            pt.X = pt.X + ((e.CellBounds.Width - Images.Images(4).Width) / 2)
            pt.Y = pt.Y + ((e.CellBounds.Height - Images.Images(4).Height) / 2)

            e.Paint(e.CellBounds, DataGridViewPaintParts.All And Not DataGridViewPaintParts.ContentForeground)
            e.Graphics.DrawImage(Images.Images(4), pt)
            e.Handled = True

        End If

        If e.RowIndex = -1 Then
            'e.Handled = False
            Exit Sub
        End If


        If e.ColumnIndex = -1 Then
            e.Handled = False
            Exit Sub
        End If

        If DataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True Then
            e.Handled = False
            Exit Sub
        End If

        If DataGrid.Columns(e.ColumnIndex).Name.Contains("_Notes") Then
            e.Handled = False
            Exit Sub
        End If

        e.Paint(e.ClipBounds, (DataGridViewPaintParts.All))

        'Notes Section
        Try
            If DataGrid.Item(DataGrid.Columns(e.ColumnIndex).Name & "_Notes", e.RowIndex).Value = 0 Then
                e.Handled = True
                Exit Sub
            End If
        Catch ex As Exception
            'this cell has no notes column so exit the sub
            e.Handled = True
            Exit Sub
        End Try

        Dim newRect As New Rectangle(e.CellBounds.X, e.CellBounds.Y, 8, 8)


        Try
            e.Graphics.FillRectangle(Brushes.Yellow, newRect)
            e.Graphics.DrawRectangle(Pens.Black, newRect)

            e.Handled = True
        Finally

        End Try


    End Sub

    Private Sub FindButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        If FireEvents = False Then Exit Sub

        TheName = SearchBox.Text.ToString

        If TheName.Contains("'").ToString Then
            TheName = TheName.Replace("'", "''")
        End If

        LoadDataGrid()

    End Sub


    Private Sub TerritoryBox_MouseHover(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoryBox.MouseHover

        ToolTip1.SetToolTip(TerritoryBox, "Use this to filter your selection by Territory")
        ToolTip1.Active = True

    End Sub

    Private Sub TypeBox_MouseHover(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TypeBox.MouseHover

        ToolTip1.SetToolTip(TypeBox, "Use this to filter your selection by Provider Type")
        ToolTip1.Active = True

    End Sub



    Private Sub DataGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGrid.CellFormatting

        If FireEvents = False Then Exit Sub

        'Set Formatting for Approved Cells
        If DataGrid.Item(e.ColumnIndex, e.RowIndex).Tag = "Approved" Then
            e.CellStyle.BackColor = Color.LightGreen
        End If

        If DataGrid.Item(e.ColumnIndex, e.RowIndex).Tag = "Rejected" Then
            e.CellStyle.BackColor = Color.PaleVioletRed
        End If

        If DataGrid.Item(e.ColumnIndex, e.RowIndex).Tag = "Unsupported" Then
            For i = 0 To Formulas.Count - 1
                If DataGrid.Columns(e.ColumnIndex).Name = Formulas(i).FieldName.ToString Then
                    e.CellStyle.BackColor = Color.LightGray
                End If
            Next

            'check if it is gray, if not then it cant be a formula then it has to be white
            If Not e.CellStyle.BackColor = Color.LightGray Then
                e.CellStyle.BackColor = Color.White
            End If
        End If

        If DataGrid.Item(e.ColumnIndex, e.RowIndex).Tag = "Supported" Then
            e.CellStyle.BackColor = Color.Orange
        End If

        If DataGrid.Item(e.ColumnIndex, e.RowIndex).Tag = "Changed" Then
            'Don't make the approved totals blue on change
            If DataGrid.Columns(e.ColumnIndex).Name <> ApprovedTotalFieldName Then
                e.CellStyle.BackColor = Color.LightBlue
            End If
        End If


        With DataGrid

            If .Columns(e.ColumnIndex).Name = "AImage" Then
                Try
                    If .Item(.Columns("A").Index, e.RowIndex).Value > 0 Then
                        e.Value = Images.Images(4)
                    Else
                        e.Value = Images.Images(5)
                    End If
                Catch ex As Exception
                    e.Value = Images.Images(5)
                End Try

            End If
        End With





    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If DataGrid.GetCellCount( _
        DataGridViewElementStates.Selected) > 0 Then

            Try

                DataGrid.SelectAll()

                ' Add the selection to the clipboard.
                Clipboard.SetDataObject( _
                    DataGrid.GetClipboardContent())

                ' Replace the text box contents with the clipboard text.
                ' DataGrid.Text = Clipboard.GetText()


                MessageBox.Show("The grid has been copied to the clipboard", "Copy complete", MessageBoxButtons.OK)

            Catch ex As System.Runtime.InteropServices.ExternalException
                DataGrid.Text = _
                    "The Clipboard could not be accessed. Please try again."
            End Try

        End If
    End Sub

    Private Sub RunReport(ByVal WithNotes As Boolean)
        Dim sql As String

        sql = "select * from dbo.Cases_View " & _
                "where Period_ID = " & PeriodBox.SelectedValue & _
                "and DataCut_ID = '" & CaseBox.SelectedValue & "' " & _
                "and Provider_Name Like '%" & Trim(SearchBox.Text) & "%' " & _
                "and RID in (select r.RID from dbo.Provider_Funding_Streams pfs left outer join dbo.Relationships r on pfs.UPIN = r.UPIN " & _
                "where r.RID is not NULL and pfs.Budget_ID = '" & TypeBox.SelectedValue & "' and pfs.period_ID = " & PeriodBox.SelectedValue & ") and value <> 0 "
        If TerritoryBox.SelectedValue <> "<All>" Then
            sql = sql & "and Territory_Code = '" & TerritoryBox.SelectedValue & "' "
        End If

        Select Case StatusBox.SelectedIndex
            Case 0
                'All
            Case 1
                'Supported
                sql = sql & "and Status = 'Supported' "
            Case 2
                'Unsupported
                sql = sql & "and Status = 'Unsupported' "
            Case 3
                'Approved
                sql = sql & "and Status = 'Approved' "
            Case 4
                'Rejected
                sql = sql & "and Status = 'Rejected' "
            Case Else

        End Select

        Dim s As String = ""

        If WithNotes Then
            s = "New_Cases_Report.rdl"
        Else
            s = "New_Cases_Report_exp.rdl"
        End If

        ShowReport("Cases Report", s, "TheStore", sql, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub ReportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReportButton.Click
        ReportsMenu.Show(ReportButton, New System.Drawing.Point(0, ReportButton.Height))

    End Sub

    Private Sub DataGrid_CellContextMenuStripNeeded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellContextMenuStripNeededEventArgs) Handles DataGrid.CellContextMenuStripNeeded

        ShowMenu = False

        If e.RowIndex = -1 Then Exit Sub
        If e.ColumnIndex = -1 Then Exit Sub
        If e.ColumnIndex < EditColumnIndex Then Exit Sub

        ShowMenu = True

        DataGrid.CurrentCell = DataGrid.Item(e.ColumnIndex, e.RowIndex)


        If DataGrid.CurrentCell.Tag Is Nothing Then
            MenuStatus = "Unsupported"
            MenuIcon = 9
        ElseIf DataGrid.CurrentCell.Tag = "Unsupported" Or DataGrid.CurrentCell.Tag.ToString = "Changed" Then
            MenuStatus = "Unsupported"
            MenuIcon = 9
        Else
            MenuStatus = DataGrid.CurrentCell.Tag.ToString
            If MenuStatus = "Approved" Then MenuIcon = 6
            If MenuStatus = "Supported" Then MenuIcon = 7
            If MenuStatus = "Rejected" Then MenuIcon = 8
        End If


    End Sub

    Private Sub CaseMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles CaseMenu.Opening

        e.Cancel = Not ShowMenu

        If DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name = ApprovedTotalFieldName Then
            e.Cancel = True
            Exit Sub
        End If

        If UserFormCheck(Me.Name) <> "W" Then
            e.Cancel = True
            Exit Sub
        End If

        If CurrentUser.Level <> 5 Then
            CurrentStatusIsToolStripMenuItem.Text = "Current Status is '" & MenuStatus & "'"
            CurrentStatusIsToolStripMenuItem.Image = Images.Images(MenuIcon)
            ToolStripSeparator2.Visible = True
            ApproveCaseToolStripMenuItem.Visible = False
            RejectCaseToolStripMenuItem.Visible = False
            SupportedCaseToolStripMenuItem.Visible = True
            SetToDraftToolStripMenuItem.Visible = True
        End If

        If CurrentUser.Level = 5 Then
            CurrentStatusIsToolStripMenuItem.Text = "Current Status is '" & MenuStatus & "'"
            CurrentStatusIsToolStripMenuItem.Image = Images.Images(MenuIcon)
            ToolStripSeparator2.Visible = True
            ApproveCaseToolStripMenuItem.Visible = True
            RejectCaseToolStripMenuItem.Visible = True
            SetToDraftToolStripMenuItem.Visible = True
            SupportedCaseToolStripMenuItem.Visible = True

        End If


    End Sub

    Private Sub ApproveCaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApproveCaseToolStripMenuItem.Click

        SetStatus("Approved")

    End Sub

    Private Sub RejectCaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RejectCaseToolStripMenuItem.Click

        SetStatus("Rejected")

    End Sub

    Private Sub SetToDraftToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SetToDraftToolStripMenuItem.Click

        SetStatus("Unsupported")

    End Sub

    Private Sub SupportedCaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SupportedCaseToolStripMenuItem.Click

        SetStatus("Supported")

    End Sub

    Private Sub SetStatus(ByVal TheStatus As String)

        Dim sql As String
        Dim Rid As String
        Dim Period As Integer
        Dim Field_Code As Integer
        Dim DataCut_Id As String
        Dim CurrentStatus As String

        With DataGrid

            DataCut_Id = TheCase
            Period = ThePeriod
            Rid = .Item("RID", .CurrentRow.Index).Value.ToString
            Field_Code = CurrentCell.FieldCode

            If DataGrid.CurrentCell.Tag IsNot Nothing Then

                CurrentStatus = DataGrid.CurrentCell.Tag.ToString

                'If the value has been changed, then it will be unsupported by default, so you should only be able to set to Supported/Unsupported
                If CurrentStatus = "Changed" Then
                    If TheStatus = "Approved" Or CurrentStatus = "Rejected" Then
                        MessageBox.Show("This case cannot be set to approved/rejected, because it is not supported by the Territory", "Cannot be Approved/Rejected", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If
                End If

                'The case must be supported by the Territory before it can be approved
                If TheStatus = "Approved" Then
                    If CurrentStatus = "Supported" Or CurrentStatus = "Rejected" Then
                        'Do Nothing
                    Else
                        MessageBox.Show("This case cannot be approved, because it is not yet supported by the Territory or it has already been approved.", "Cannot be Approved", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If
                End If

                'The case can only be rejected if it has already been supported by the Territory or approved by NO
                If TheStatus = "Rejected" Then
                    If CurrentStatus = "Approved" Or CurrentStatus = "Supported" Then
                        'Do Nothing
                    Else
                        MessageBox.Show("This case cannot be rejected, because it is not supported by the Territory or it has already been rejected.", "Cannot be Rejected", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If
                End If

                'Once approved/rejected the case cannot be Supported by the Territory. 
                If TheStatus = "Supported" Or TheStatus = "Unsupported" Then
                    If CurrentStatus = "Approved" Or CurrentStatus = "Rejected" Then
                        MessageBox.Show("This case cannot be set to supported/unsupported, because it has already been approved/rejected", "Cannot be Supported", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If
                End If

            Else

                'If there is no tag, then you should only be able to set to Supported/Unsupported
                If TheStatus = "Approved" Or TheStatus = "Rejected" Then
                    MessageBox.Show("This case cannot be set to approved/rejected, because it is not supported by the Territory", "Cannot be Approved/Rejected", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                End If

            End If

            'If there are no formulas in the case then definitely write back the status
            ' If Formulas.Count = 0 Then WriteBack(TheStatus)
            If Formulas.Count = 0 Then
                sql = "Update Modelling_Case_Data Set Status = '" & TheStatus & "' where RID = '" & Rid & "' and Period_ID = " & ThePeriod & " and DataCut_ID = '" & TheCase & "' " & _
                    "and Field_Code = " & Field_Code & " and TheDate = (Select Max(TheDate) from Modelling_Case_Data where RID = '" & Rid & "' and DataCut_ID = '" & TheCase & "' " & _
                    "and Field_Code = " & Field_Code & " and Period_ID = " & Period & ")"
                RunNonQuery(sql)
            End If

            'If there are formulas, checkto see if cell is in a formula column, if so do not want to write back value to database
            For i = 0 To Formulas.Count - 1

                If DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name <> Formulas(i).FieldName.ToString Then

                    sql = "Update Modelling_Case_Data Set Status = '" & TheStatus & "' where RID = '" & Rid & "' and Period_ID = " & ThePeriod & " and DataCut_ID = '" & TheCase & "' " & _
                    "and Field_Code = " & Field_Code & " and TheDate = (Select Max(TheDate) from Modelling_Case_Data where RID = '" & Rid & "' and DataCut_ID = '" & TheCase & "' " & _
                    "and Field_Code = " & Field_Code & " and Period_ID = " & Period & ")"
                    RunNonQuery(sql)

                Else

                    WriteBack(TheStatus)

                End If

            Next

            .CurrentCell.Tag = TheStatus

            If TheStatus = "Approved" Or TheStatus = "Rejected" Then
                UpdateApprovedValues()
                UpdateScenarioTables(SaveStuffCauseImNotLoading)
            End If

        End With

    End Sub

    Private Sub SearchBox_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles SearchBox.KeyPress

        If e.KeyChar = Chr(13) Then

            If FireEvents = False Then Exit Sub

            TheName = SearchBox.Text.ToString

            If TheName.Contains("'").ToString Then
                TheName = TheName.Replace("'", "''")
            End If

            LoadDataGrid()

        End If

    End Sub

    Private Sub GetStatus(ByVal Cell As Boolean, ByVal CellRID As String)

        Dim sql As String
        Dim TheData As DataView
        Dim WhereClause As String

        If Cell = True Then

            WhereClause = " and RID = '" & CellRID & "' "

        Else

            WhereClause = " "

        End If


        sql = "Declare @TheCase varchar(35) " & _
              "Declare @ThePeriod Int " & _
              "Set @TheCase = '" & TheCase & "' " & _
              "Set @ThePeriod = " & ThePeriod & " " & _
                "Select sub.*, d.Status, d.Value from " & _
                        "(select DataCut_ID, Period_ID, RID, Field_Code, Max(TheDate) as TheDate from modelling_case_data " & _
                        "where DataCut_ID = @TheCase " & _
                        "and Period_ID = @ThePeriod " & _
                        WhereClause & _
                        "group by DataCut_ID, Period_ID, RID, Field_Code) sub " & _
                " Inner Join  modelling_case_data d  " & _
                " on (d.DataCut_ID = sub.DataCut_Id " & _
                " and d.Period_ID = sub.Period_ID " & _
                " and d.RID = sub.RID " & _
                " and d.Field_Code = sub.Field_Code " & _
                " and d.TheDate = sub.TheDate) " & _
                " where d.DataCut_ID = @TheCase " & _
                " and d.Period_ID = @ThePeriod " & _
                " and d.Status = 'Approved' or d.Status = 'Rejected' or d.Status = 'Supported' "

        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then Exit Sub

        For r = 0 To TheData.Count - 1

            'Find the cell
            Dim TheRid As String
            Dim TheFieldCode As String
            Dim TheStatus As String
            Dim TheValue As Decimal


            TheRid = Trim(TheData(r).Item("RID").ToString)
            TheFieldCode = TheData(r).Item("Field_Code")
            TheStatus = Trim(TheData(r).Item("Status").ToString)
            TheValue = TheData(r).Item("Value")

            Dim TheFieldName As String

            TheFieldName = FieldName(TheFieldCode)

            'Now find the cell in the grid and update its tag
            For i = 0 To DataGrid.Rows.Count - 1

                If DataGrid.Item("RID", i).Value.ToString = TheRid And Math.Round(DataGrid.Item(TheFieldName, i).Value, 6) = TheValue Then
                    DataGrid.Item(TheFieldName, i).Tag = TheStatus

                    If DataGrid.Item(TheFieldName, i).Tag = "Approved" Then
                        DataGrid.Item(TheFieldName, i).Style.BackColor = Color.LightGreen
                    End If

                    If DataGrid.Item(TheFieldName, i).Tag = "Rejected" Then
                        DataGrid.Item(TheFieldName, i).Style.BackColor = Color.PaleVioletRed
                    End If

                    If DataGrid.Item(TheFieldName, i).Tag = "Supported" Then
                        DataGrid.Item(TheFieldName, i).Style.BackColor = Color.Orange
                    End If

                End If

            Next

        Next

       

    End Sub

    Private Sub UpdateApprovedValues()

        If ApprovedTotalFieldName.Length = 0 Then Exit Sub

        Dim TheTotal As Decimal = 0
        Dim r, c As Integer

        DidCellReallyChange = False

        For r = 0 To DataGrid.Rows.Count - 1

            For c = 0 To DataGrid.Columns.Count - 1

                If DataGrid.Item(c, r).Tag = "Approved" Then
                    If DataGrid.Columns(c).Name <> ApprovedTotalFieldName Then
                        TheTotal = TheTotal + DataGrid.Item(c, r).Value
                    End If
                End If

            Next

            DataGrid.Item(ApprovedTotalFieldName, r).Value = TheTotal


            If TheTotal > 0 Then
                DataGrid.Item(ApprovedTotalFieldName, r).Style.BackColor = Color.LightGreen

            Else
                DataGrid.Item(ApprovedTotalFieldName, r).Style.BackColor = Color.LightGray
            End If

            TheTotal = 0

        Next

        DidCellReallyChange = True
    End Sub

    Private Sub UpdateScenarioTables(ShouldUpdate As Boolean)

        If ShouldUpdate = False Then Exit Sub

        If DataGrid.CurrentRow Is Nothing Then Exit Sub

        If FireEvents = False Then Exit Sub
        If ApprovedTotalFieldName.Length = 0 Then Exit Sub

        Dim sql As String
        Dim TheData As DataView

        Dim TheRID As String
        Dim TheValue As Decimal
        Dim TheTable As String
        Dim TheDescripton As String

        'Find TheDescription
        sql = "Select Description from Modelling_Case_Datasets where DataCut_ID = '" & TheCase & "' and Period_ID = " & ThePeriod
        TheData = LoadDataView(sql)
        If TheData.Count <> 1 Then Exit Sub
        TheDescripton = Trim(TheData(0).Item("Description".ToString))

        'Find Modelling_Table
        sql = "Select Modelling_Table from Modelling_Case_Templates where DataCut_ID = '" & TheCase & "' and Period_ID = " & ThePeriod & " and Field_Name = '" & ApprovedTotalFieldName & "'"
        TheData = LoadDataView(sql)
        If TheData.Count <> 1 Then Exit Sub
        TheTable = Trim(TheData(0).Item("Modelling_Table").ToString)

        TheRID = DataGrid.Item("RID", DataGrid.CurrentRow.Index).Value.ToString
        TheValue = DataGrid.Item(ApprovedTotalFieldName, DataGrid.CurrentRow.Index).Value.ToString

        'Firstly clear out any exisiting values from the table

        sql = "Delete from " & TheTable & " Where DataCut_ID = '" & TheCase & "' and RID = '" & TheRID & "' and Source = 'Case'"

        RunNonQuery(sql)

        If TheValue <> 0 Then

            'Add the new data into the table

            sql = "Insert into " & TheTable & " Values ('" & TheCase & "', '" & TheRID & "', 'Case', 'Approved', '" & TheDescripton & "', " & TheValue & ")"

            RunNonQuery(sql)

        End If

    End Sub

    Private Sub WriteBack(ByVal TheStatus As String)

        Dim r, c As Integer
        Dim sql As String = ""
        Dim TheRid As String
        Dim TheValue As Decimal
        Dim TheFieldCode As Integer
        Dim TheFieldName As String
        Dim TheDate As Double = Now.ToOADate

        With DataGrid

            If DataGrid.CurrentCell.ColumnIndex < EditColumnIndex Then Exit Sub

            c = DataGrid.CurrentCell.ColumnIndex
            r = DataGrid.CurrentCell.RowIndex

            TheFieldName = DataGrid.Columns(c).Name.ToString
            TheFieldCode = FieldCode(TheFieldName)
            TheRid = DataGrid.Item("RID", r).Value.ToString
            TheValue = DataGrid.Item(c, r).Value

            sql = "Insert into Modelling_Case_Data Values ('" & TheCase & "', " & ThePeriod & ", '" & TheRid & "', " & TheFieldCode & ", " & _
                TheDate & ", '" & GetUserName & "', '" & TheStatus & "', " & TheValue & ") "

            RunNonQuery(sql)

        End With


    End Sub


    Private Sub NewCasesForm_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Enter

        GetStatus(False, "")

    End Sub

    Private Sub ReportWithNotesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReportWithNotesToolStripMenuItem.Click

        RunReport(True)

    End Sub

    Private Sub ReportWithoutNotesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReportWithoutNotesToolStripMenuItem.Click

        RunReport(False)

    End Sub

    Private Sub DataGrid_ColumnHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGrid.ColumnHeaderMouseClick

        ' Check which column is selected, otherwise set NewColumn to Nothing.
        Dim newColumn As DataGridViewColumn

        'If DataGrid.Columns.GetColumnCount(DataGridViewElementStates.Selected) = 1 Then
        '    newColumn = DataGrid.SelectedColumns(0)
        'Else
        '    newColumn = Nothing
        'End If

        newColumn = DataGrid.Columns(e.ColumnIndex)


        Dim oldColumn As DataGridViewColumn = DataGrid.SortedColumn
        Dim direction As ListSortDirection

        ' If oldColumn is null, then the DataGridView is not currently sorted.
        If oldColumn IsNot Nothing Then

            ' Sort the same column again, reversing the SortOrder.
            If oldColumn Is newColumn AndAlso DataGrid.SortOrder = _
                SortOrder.Ascending Then
                direction = ListSortDirection.Descending
            Else

                ' Sort a new column and remove the old SortGlyph.
                direction = ListSortDirection.Ascending
                oldColumn.HeaderCell.SortGlyphDirection = SortOrder.None
            End If
        Else
            direction = ListSortDirection.Ascending
        End If


        ' If no column has been selected, display an error dialog  box.
        If newColumn Is Nothing Then
            MessageBox.Show("Select a single column and try again.", _
                "Error: Invalid Selection", MessageBoxButtons.OK, _
                MessageBoxIcon.Error)
        Else
            DataGrid.Sort(newColumn, direction)
            If direction = ListSortDirection.Ascending Then
                newColumn.HeaderCell.SortGlyphDirection = SortOrder.Ascending
            Else
                newColumn.HeaderCell.SortGlyphDirection = SortOrder.Descending
            End If
        End If


        GetStatus(False, "")
        UpdateApprovedValues()
        DataGrid.Refresh()

    End Sub

End Class